<?php
/**
 * @Author    紫梦
 * @DateTime  2018-02-02
 * @copyright 响响租车
 * @version   1.0
 */
namespace app\index\controller;
use think\Exception;
use \think\Request;
use app\common\model\Cars;
use app\common\model\Secure;
use app\common\model\InstallDevices;
use app\common\model\InstallUser;
use app\common\model\Agency;
use app\common\model\Brand;
use app\common\model\CarsInfo;
use app\common\model\OffAbnormal;
use app\common\model\OffNormal;
use app\common\model\OffRecord;
class Excell extends Base
{
	public $excel;

	public function cars()
	{
		$request = Request::instance();
		$get = $request->get();

        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('车辆基础数据');
        $this->excel = $PHPExcel;
        //填入表头
        $PHPExcel->getActiveSheet()->setCellValue('A1', '车牌号码');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '车架号');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '发动机号');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '车辆型号');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '汽车品牌');
        $PHPExcel->getActiveSheet()->setCellValue('F1', '车辆类型');
        $PHPExcel->getActiveSheet()->setCellValue('G1', '车辆用途');
        $PHPExcel->getActiveSheet()->setCellValue('H1', '车辆状态');
        $PHPExcel->getActiveSheet()->setCellValue('I1', '机动车所属权');
        $PHPExcel->getActiveSheet()->setCellValue('J1', '所属加盟商');
        $PHPExcel->getActiveSheet()->setCellValue('K1', '停车场');

		if( !empty($get['datemin']) && !empty($get['datemax']) ){
                $where['create_time'] = ['between time',[$get['datemin'],$get['datemax']]];                  
                    
        }
        if ( empty($get['datemin']) && !empty($get['datemax']) ){             
            $where['create_time'] = ['<= time',$get['datemax']];
                
                
        }
        if ( !empty($get['datemin']) && empty($get['datemax']) ) {
            $where['create_time'] = ['> time',$get['datemin']];
             
        }
        if ( !empty($get['search_inpute']) ) {
            $where['car_sn|car_unique_id|cardcode|equipment_id'] = ['like','%'.$get['search_inpute'].'%'];
                
        }
        if ( !empty($get['car_usage']) ) {
            $where['car_usage'] = $get['car_usage'];     
                
        }

        if ( !empty($get['relevance']) ) {
            $where['relevance'] = $get['relevance'];     
                
        }
        if ( !empty($get['agency_id']) ) {
            $where['agency_id'] = $get['agency_id'];
                
        }
        if ( !empty($get['brand_id']) ) {
            $where['brand_id'] = $get['brand_id'];
                
        }
        if ( !empty($get['supplier_no']) ) {
            $where['supplier_no'] = $get['supplier_no'];
                
        }
        if ( !empty($get['sim_card']) ) {
            $where['sim_card'] = $get['sim_card'];
                
        }
        
        if( empty($where) ){
        	$where = '1=1';
        }    
		try {			
			Cars::with('brand,agency')->where($where)->chunk(100,function($model){
                $car_breed=['小轿车','SUV','中巴'];
                $car_usage = ['未知','库存','分时租赁','长租','以租代购','企业用车','共享用车','公务用车','自测用车','销售'];
                $relevance = ['未知','待整备','整备完成','分时租赁','企业用车','共享用车','长租','自测用车','以租代购','在修待修','体验用车','公务用车','销售售出','展示用车','待售出','办理违章','出险','紧急调用','设备故障'];
                $car_park = ['未知','香雪停车场','独秀苑停车场','双鱼汇停车场','金宏别克汽车园区停车场','静兰停车场'];

                static $num = 1;
                    
                foreach($model as $cars){
			   
			    	$num ++;
					$this->excel->getActiveSheet()->setCellValue("A".$num."","".$cars->car_sn."");
					$this->excel->getActiveSheet()->setCellValue("B".$num."","".$cars->car_unique_id."");
					$this->excel->getActiveSheet()->setCellValue("C".$num."","".$cars->motor_id."");
					$this->excel->getActiveSheet()->setCellValue("D".$num."","".$cars->car_name."");
					$this->excel->getActiveSheet()->setCellValue("E".$num."","".$cars->brand['name']."");
					$this->excel->getActiveSheet()->setCellValue("F".$num."","".$car_breed[$cars->car_breed]."");
                    $this->excel->getActiveSheet()->setCellValue("G".$num."","".$car_usage[$cars->car_usage??0]."");
			    	$this->excel->getActiveSheet()->setCellValue("H".$num."","".$relevance[$cars->relevance??0]."");
			    	$this->excel->getActiveSheet()->setCellValue("I".$num."","".$cars->holder_name."");
			    	$this->excel->getActiveSheet()->setCellValue("J".$num."","".$cars->agency['name']."");
                    $this->excel->getActiveSheet()->setCellValue("K".$num."","".$car_park[$cars->car_park??0]."");
                    // dump($cars->brand['name']);

			    }
			});			
		} catch (\Exception $e) {			
			echo $e->getMessage();
		}

    		$PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
            header('Content-Disposition: attachment;filename="'.date('Y-m-d',time()).'车辆基础数据.xlsx"');
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $PHPWriter->save("php://output");exit;
		
	}

    public function secure($secure_end='',$secure_type='',$secure_company='',$search_inpute='',$status='')
    {
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('保险数据');
        $this->excel = $PHPExcel;
        //填入表头
        $PHPExcel->getActiveSheet()->setCellValue('A1', '车牌号');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '车架号');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '保险类型');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '保险公司');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '生效日期');
        $PHPExcel->getActiveSheet()->setCellValue('F1', '失效日期');
        $PHPExcel->getActiveSheet()->setCellValue('G1', '保险费用');
        $PHPExcel->getActiveSheet()->setCellValue('H1', '备注');

        if( !empty($secure_end) ){
                $where['secure_end'] = ['<= time',$secure_end];
                $where['status'] = 1;
        }
        if( !empty($secure_type) ){
            $where['secure_type'] = $secure_type;
        }
        if( !empty($secure_company) ){
            $where['secure_company'] = $secure_company;
        }
        if( !empty($search_inpute) ){
            $car_id = Cars::where( ['car_sn|car_unique_id' => ['like','%'.$search_inpute.'%']] )
                            ->column('car_id');           
            $where['car_id'] = ['in',$car_id];
        }
        if( !empty($status) ){
            $where['status'] = $status;
        }
        if( empty($where) ){
            $where = '1=1';
        }

        try {
            Secure::where($where)->chunk(100,function($model){
                static $num = 1;
                $secure_type = ['未知','交强险','商业保险','承运人责任险'];   
                $secure_company = ['未知','平安保险','阳光保险','中银保险','中保保险','中华联合保险','太平洋保险'];   
                foreach($model as $secure){
                    $num ++;
                    $this->excel->getActiveSheet()->setCellValue("A".$num."","".$secure->cars_info->car_sn."");
                    $this->excel->getActiveSheet()->setCellValue("B".$num."","".$secure->cars_info->car_unique_id."");
                    $this->excel->getActiveSheet()->setCellValue("C".$num."","".$secure_type[$secure->secure_type??0]."");
                    $this->excel->getActiveSheet()->setCellValue("D".$num."","".$secure_company[$secure->secure_company??0]."");
                    $this->excel->getActiveSheet()->setCellValue("E".$num."","".$secure->secure_start."");
                    $this->excel->getActiveSheet()->setCellValue("F".$num."","".$secure->secure_end."");
                    $this->excel->getActiveSheet()->setCellValue("G".$num."","".$secure->secure_money."");
                    $this->excel->getActiveSheet()->setCellValue("H".$num."","".$secure->secure_desc."");
                }
            });
        } catch (Exception $e) {
            echo $e->getMessage();
        }

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="'.date('Y-m-d',time()).'保险数据.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");exit;
    }

    public function installUser(){
        $request = Request::instance();
        $get = $request->get();
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('安装人员数据');
        $this->excel = $PHPExcel;

        $PHPExcel->getActiveSheet()->setCellValue('A1', '车牌号');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '车架号');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '智能设备号');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '安装类型');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '安装人员');
        $PHPExcel->getActiveSheet()->setCellValue('F1', '安装时间');

        if( !empty($get['datemin']) && !empty($get['datemax']) ){
                $where['create_time'] = ['between time',[$get['datemin'],$get['datemax']]];                  
                    
        }
        if ( empty($get['datemin']) && !empty($get['datemax']) ){             
            $where['create_time'] = ['<= time',$get['datemax']];
                
                
        }
        if ( !empty($get['datemin']) && empty($get['datemax']) ) {
            $where['create_time'] = ['> time',$get['datemin']];
             
        }
        if ( !empty($get['search_inpute']) ) {
            $where['car_id'] = ['in',Cars::where([
                    'car_sn|car_unique_id|cardcode|equipment_id|sim_id' => ['like','%'.$get['search_inpute'].'%']
                    ])->column('car_id')];
                
        }
        if ( !empty($get['type']) ) {
            $where['type'] = $get['type'];     
                
        }
        if ( !empty($get['user_id']) ) {
            $where['user_id'] = $get['user_id'];
                
        }
        
        if( empty($where) ){
            $where = '1=1';
        } 

        try {           
            InstallDevices::with('cars,terminal,installUser')->where($where)->chunk(100,function($model){
                $type=['','新装','更换'];
                static $num = 1;
                    
                foreach($model as $install){
                    $num ++;
                    $this->excel->getActiveSheet()->setCellValue("A".$num."","".$install->cars['car_sn']."");
                    $this->excel->getActiveSheet()->setCellValue("B".$num."","".$install->cars['car_unique_id']."");
                    $this->excel->getActiveSheet()->setCellValue("C".$num."","".$install->terminal['equipment_id']."");
                    $this->excel->getActiveSheet()->setCellValue("D".$num."","".$type[$install->type??0]."");
                    $this->excel->getActiveSheet()->setCellValue("E".$num."","".$install->installUser['name']."");
                    $this->excel->getActiveSheet()->setCellValue("F".$num."","".$install->create_time."");
                    
                }
            }); 
        } catch (\Exception $e) {           
            echo $e->getMessage();
        }   

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="'.date('Y-m-d',time()).'安装人员数据.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");exit;

    }

    public function off(){
        $request = Request::instance();
        $get = $request->get();
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('未知离线车辆数据');
        $this->excel = $PHPExcel;

        $PHPExcel->getActiveSheet()->setCellValue('A1', '车牌号');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '车架号');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '品牌名称');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '加盟商');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '车机号');
        $PHPExcel->getActiveSheet()->setCellValue('F1', '卡号');
        $PHPExcel->getActiveSheet()->setCellValue('G1', '卡序列号');
        $PHPExcel->getActiveSheet()->setCellValue('H1', '车机型号');
        $PHPExcel->getActiveSheet()->setCellValue('I1', '离线原因');
        $PHPExcel->getActiveSheet()->setCellValue('J1', '车机时间');
        
        if( !empty($get['datemin']) && !empty($get['datemax']) ){
                $where['create_time'] = ['between time',[$get['datemin'],$get['datemax']]];                  
                    
        }
        if ( empty($get['datemin']) && !empty($get['datemax']) ){             
            $where['create_time'] = ['<= time',$get['datemax']];
                
                
        }
        if ( !empty($get['datemin']) && empty($get['datemax']) ) {
            $where['create_time'] = ['> time',$get['datemin']];
             
        }

        if ( !empty($get['agency_id']) ) {
            $where['agency_id'] = $get['agency_id'];
                
        }
        if ( !empty($get['brand_id']) ) {
            $where['brand_id'] = $get['brand_id'];
                
        }
        if ( !empty($get['supplier_no']) ) {
            $where['supplier_no'] = $get['supplier_no'];
                
        }

        if ( !empty($get['search_inpute']) ) {
            $where['car_sn|car_unique_id|cardcode|equipment_id|sim_id'] = ['like','%'.$get['search_inpute'].'%'];
                
        }
        
        if( empty($where) ){
            $where = '1=1';
        } 
       
        try {           
            OffAbnormal::with('brand,agency')->where($where)->chunk(100,function($model){
                
                static $num = 1;  
                $supplier_no = ['1376920002'=>'S','1376920001'=>'W','1376920003'=>'S-IOT'];
                foreach($model as $value){

                    $num ++;
                    $this->excel->getActiveSheet()->setCellValue("A".$num."","".$value->car_sn."");
                    $this->excel->getActiveSheet()->setCellValue("B".$num."","".$value->car_unique_id."");
                    $this->excel->getActiveSheet()->setCellValue("C".$num."","".$value->brand['name']."");
                    $this->excel->getActiveSheet()->setCellValue("D".$num."","".$value->agency['name']."");
                    $this->excel->getActiveSheet()->setCellValue("E".$num."","".$value->equipment_id."");
                    $this->excel->getActiveSheet()->setCellValue("F".$num."","".$value->cardcode."");
                    $this->excel->getActiveSheet()->setCellValue("G".$num."","".$value->sim_id."");
                    if( in_array( $value->supplier_no, array_keys($supplier_no) ) ){
                        $this->excel->getActiveSheet()->setCellValue("H".$num."","".$supplier_no[$value->supplier_no]."");

                    }
                    $this->excel->getActiveSheet()->setCellValue("J".$num."","".date('Y-m-d H:i:s', $value->machine_report_time/ 1000)."");

                }
            }); 
        } catch (\Exception $e) {           
            echo $e->getMessage();
        }   

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="'.date('Y-m-d',time()).'未知离线车辆数据.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");exit;
    }


    public function normal(){
        $request = Request::instance();
        $get = $request->get();
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('已知离线车辆数据');
        $this->excel = $PHPExcel;

        $PHPExcel->getActiveSheet()->setCellValue('A1', '车牌号');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '车架号');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '品牌名称');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '加盟商');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '车机号');
        $PHPExcel->getActiveSheet()->setCellValue('F1', '卡号');
        $PHPExcel->getActiveSheet()->setCellValue('G1', '卡序列号');
        $PHPExcel->getActiveSheet()->setCellValue('H1', '车机型号');
        $PHPExcel->getActiveSheet()->setCellValue('I1', '车机时间');
        
        if( !empty($get['datemin']) && !empty($get['datemax']) ){
                $where['create_time'] = ['between time',[$get['datemin'],$get['datemax']]];                  
                    
        }
        if ( empty($get['datemin']) && !empty($get['datemax']) ){             
            $where['create_time'] = ['<= time',$get['datemax']];
                
                
        }
        if ( !empty($get['datemin']) && empty($get['datemax']) ) {
            $where['create_time'] = ['> time',$get['datemin']];
             
        }

        if ( !empty($get['agency_id']) ) {
            $where['agency_id'] = $get['agency_id'];
                
        }
        if ( !empty($get['brand_id']) ) {
            $where['brand_id'] = $get['brand_id'];
                
        }
        if ( !empty($get['supplier_no']) ) {
            $where['supplier_no'] = $get['supplier_no'];
                
        }

        if ( !empty($get['search_inpute']) ) {
            $where['car_sn|car_unique_id|cardcode|equipment_id|sim_id'] = ['like','%'.$get['search_inpute'].'%'];
                
        }
        
        if( empty($where) ){
            $where = '1=1';
        } 
       
        try {           
            OffNormal::with('brand,agency')->where($where)->chunk(100,function($model){
                
                static $num = 1;  
                $supplier_no = ['1376920002'=>'S','1376920001'=>'W','1376920003'=>'S-IOT'];
                foreach($model as $value){

                    $num ++;
                    $this->excel->getActiveSheet()->setCellValue("A".$num."","".$value->car_sn."");
                    $this->excel->getActiveSheet()->setCellValue("B".$num."","".$value->car_unique_id."");
                    $this->excel->getActiveSheet()->setCellValue("C".$num."","".$value->brand['name']."");
                    $this->excel->getActiveSheet()->setCellValue("D".$num."","".$value->agency['name']."");
                    $this->excel->getActiveSheet()->setCellValue("E".$num."","".$value->equipment_id."");
                    $this->excel->getActiveSheet()->setCellValue("F".$num."","".$value->cardcode."");
                    $this->excel->getActiveSheet()->setCellValue("G".$num."","".$value->sim_id."");
                    if( in_array( $value->supplier_no, array_keys($supplier_no) ) ){
                        $this->excel->getActiveSheet()->setCellValue("H".$num."","".$supplier_no[$value->supplier_no]."");

                    }
                    $this->excel->getActiveSheet()->setCellValue("I".$num."","".date('Y-m-d H:i:s', $value->machine_report_time/ 1000)."");

                }
            }); 
        } catch (\Exception $e) {           
            echo $e->getMessage();
        }   

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="'.date('Y-m-d',time()).'已知离线车辆数据.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");exit;
    }


    public function record(){
        $request = Request::instance();
        $get = $request->get();
        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('离线记录数据');
        $this->excel = $PHPExcel;

        $PHPExcel->getActiveSheet()->setCellValue('A1', '车牌号');
        $PHPExcel->getActiveSheet()->setCellValue('B1', '车架号');
        $PHPExcel->getActiveSheet()->setCellValue('C1', '品牌名称');
        $PHPExcel->getActiveSheet()->setCellValue('D1', '加盟商');
        $PHPExcel->getActiveSheet()->setCellValue('E1', '车机号');
        $PHPExcel->getActiveSheet()->setCellValue('F1', '卡号');
        $PHPExcel->getActiveSheet()->setCellValue('G1', '卡序列号');
        $PHPExcel->getActiveSheet()->setCellValue('H1', '车机型号');
        $PHPExcel->getActiveSheet()->setCellValue('I1', '离线原因');
        $PHPExcel->getActiveSheet()->setCellValue('J1', '免提醒');
        $PHPExcel->getActiveSheet()->setCellValue('K1', '车机时间');
        $PHPExcel->getActiveSheet()->setCellValue('L1', '记录时间');

        if( !empty($get['datemin']) && !empty($get['datemax']) ){
                $where['create_time'] = ['between time',[$get['datemin'],$get['datemax']]];                  
                    
        }
        if ( empty($get['datemin']) && !empty($get['datemax']) ){             
            $where['create_time'] = ['<= time',$get['datemax']];
                
                
        }
        if ( !empty($get['datemin']) && empty($get['datemax']) ) {
            $where['create_time'] = ['> time',$get['datemin']];
             
        }

        if ( !empty($get['type']) ) {
            $where['type'] = $get['type'];
                    
        }
        if ( !empty($get['search_inpute']) ) {
            $where['car_sn|car_unique_id|cardcode|equipment_id|sim_id'] = ['like','%'.$get['search_inpute'].'%'];
                
        }
        
        if( empty($where) ){
            $where = '1=1';
        } 

        try {           
            OffRecord::with('brand,agency')->where($where)->chunk(100,function($model){
               
                static $num = 1;  
                $supplier_no = ['1376920002'=>'S','1376920001'=>'W','1376920003'=>'S-IOT'];
                foreach($model as $value){
                    $num ++;
                    $this->excel->getActiveSheet()->setCellValue("A".$num."","".$value->car_sn."");
                    $this->excel->getActiveSheet()->setCellValue("B".$num."","".$value->car_unique_id."");
                    $this->excel->getActiveSheet()->setCellValue("C".$num."","".$value->brand['name']."");
                    $this->excel->getActiveSheet()->setCellValue("D".$num."","".$value->agency['name']."");
                    $this->excel->getActiveSheet()->setCellValue("E".$num."","".$value->equipment_id."");
                    $this->excel->getActiveSheet()->setCellValue("F".$num."","".$value->cardcode."");
                    $this->excel->getActiveSheet()->setCellValue("G".$num."","".$value->sim_id."");
                    if( in_array( $value->supplier_no, array_keys($supplier_no) ) ){
                        $this->excel->getActiveSheet()->setCellValue("H".$num."","".$supplier_no[$value->supplier_no]."");

                    }
                    $this->excel->getActiveSheet()->setCellValue("I".$num."","".$value->reason."");
                    $this->excel->getActiveSheet()->setCellValue("J".$num."","".($value->time/86400)."天"."");
                    $this->excel->getActiveSheet()->setCellValue("K".$num."","".date('Y-m-d H:i:s', $value->machine_report_time/ 1000)."");
                    $this->excel->getActiveSheet()->setCellValue("L".$num."","".$value->create_time."");

                }
            }); 
        } catch (\Exception $e) {           
            echo $e->getMessage();
        }   

        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename="'.date('Y-m-d',time()).'离线记录数据.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");exit;
    }
}